#include "studentsql.h"

StudentSql::StudentSql()
{
    db = QSqlDatabase::addDatabase("QMYSQL");//添加mysql
}

void StudentSql::ConnectSql(const QString &hostname)
{
    db.setHostName(hostname);
    db.setUserName("root");
    db.setPassword("123456");
    db.setDatabaseName("test268");
    db.setPort(3306);
    if(!db.open())
    {
        qDebug() << db.lastError() <<endl;
        return ;
    }
    qDebug() << "打开成功\n";
}

void StudentSql::SetFakeStuInfo()
{
    QSqlQuery query(db);
    //删除现有表
    query.exec("drop table log;");
    query.exec("drop table student_list;");
    query.exec("drop table StuInfo;");
    query.exec("drop table Lesson;");
    query.exec("drop table Score;");

    //创建新表
    query.exec("create table log(id int primary key,username text, passwd text,image longblob,name text,StuId int,TchId int);");
    query.exec("create table student_list(class int,id int primary key,Name text,Sex text,teacher text,teacherId text);");
    query.exec("create table StuInfo(id int primary key,name text, sex text,birthday date,city text,residence text,phone text,email text, stageId text);");
    query.exec("create table Lesson(id int primary key,name text);");
    query.exec("create table Score(id int primary key,StuId text,StageId text,Score int, comment text);");
    //登录表
    query.exec("insert into log(id,username,passwd,name,TchId) values(1,'123456789@qq.com','123456abc@','吕冠钟',02680100)");
    query.exec("insert into log(id,username,passwd,name,StuId) values(2,'123456@163.com','abc123456#','张三',02680001)");
    query.exec("insert into log(id,username,passwd,name,StuId) values(3,'5201314@qq.com','666666@abc','李四',02680002)");
    //老师表
    query.exec("insert into student_list(class,id,Name,Sex,teacher,teacherId) values(268,1,'吕冠钟','男','吕冠钟','123456789@qq.com')");
    query.exec("insert into student_list(class,id,Name,Sex,teacher) values(268,2,'张三','男','吕冠钟')");
    query.exec("insert into student_list(class,id,Name,Sex,teacher) values(268,3,'李四','男','吕冠钟')");
    //学生信息表
    query.exec("insert into StuInfo(id,name,sex,birthday,city,residence,phone,email,stageId) values(1,'张三','男','2002-04-28','江苏南京','安徽合肥','11223344','123456@163.com','1001')");
    query.exec("insert into StuInfo(id,name,sex,birthday,city,residence,phone,email,stageId) values(2,'李四','男','2002-08-30','江苏南京','上海','12345676','5201314@qq.com','1002')");
    //学习阶段表
    query.exec("insert into Lesson(id,name) values(1001,'第一阶段');");
    query.exec("insert into Lesson(id,name) values(1002,'第二阶段');");
    query.exec("insert into Lesson(id,name) values(1003,'第三阶段');");
    //成绩评价表
    query.exec("insert into Score(id,StuId,StageId) values(1,'123456@163.com','1001')");
    query.exec("insert into Score(id,StuId,StageId,Score,comment) values(2,'5201314@qq.com','1001',90,'整挺好')");
    query.exec("insert into Score(id,StuId,StageId,Score,comment) values(3,'5201314@qq.com','1002',95,'发挥稳定')");

}

void StudentSql::SavePhotoToSql(const QString& ImagePath)
{
    QByteArray imageInfo;
    QBuffer buffer(&imageInfo);
    buffer.open(QIODevice::WriteOnly);

    QSqlQuery query(db);
    QString SavePhoto = "update image set image = :imageData where uesrname = " + ImagePath;

    query.prepare(SavePhoto);
    query.bindValue(":imageData",imageInfo);

    query.exec();
}

void StudentSql::SetStuId(const QString& StuId)
{
    this->StuId = StuId;
}

void StudentSql::GetInfoFromSql(QStringList &l)
{
    QSqlQuery query(db);

    QString Key = StuId;//主键
    QString table = "StuInfo";//表名

    QString Getname = "select name from " + table + " where id = " + Key;
    QString Getsex = "select sex from " + table + " where id = " + Key;
    QString Getbirthday = "select birthday from " + table + " where id = " + Key;
    QString Getcity = "select city from " + table + " where id = " + Key;
    QString Getresidence = "select residence from " + table + " where id = " + Key;
    QString Getphone = "select phone from " + table + " where id = " + Key;
    QString Getemail = "select email from " + table + " where id = " + Key;

    QStringList list;
    list.push_back(std::move(Getname));
    list.push_back(std::move(Getsex));
    list.push_back(std::move(Getbirthday));
    list.push_back(std::move(Getcity));
    list.push_back(std::move(Getresidence));
    list.push_back(std::move(Getphone));
    list.push_back(std::move(Getemail));

    int i = 0;
    for(auto ite : list)
    {
        if(query.exec(ite))
        {
            query.next();
            l[i] = query.value(0).toString();
        }
        i++;
    }
}

void StudentSql::GetScoreFromSql(std::vector<std::pair<std::string, int> > &v)
{
    QSqlQuery query(db);
    QString GetScore = "select LesId,Score from Score where StuId = " + StuId;

    if(query.exec(GetScore))
    {
        while(query.next())
        {
            QString LesId = query.value("LesId").toString();

            QSqlQuery tmpquery(db);
            LesId = "select name from Lesson where id = " + LesId;
            tmpquery.exec(LesId);
            if(tmpquery.next())
            {
                std::string LesName = tmpquery.value("name").toString().toStdString();
                int score = query.value("Score").toInt();
                std::pair<std::string,int> pair = {LesName,score};
                v.push_back(std::move(pair));
            }

        }
    }
}

void StudentSql::GetCommentFromSql(QString &str)
{
    QSqlQuery query;
    QString GetComment = "select comment from StuInfo where id = " + StuId;

    if(query.exec(GetComment))
        if(query.next())
            str += query.value("comment").toString();
}

void StudentSql::GetStageFromSql(QString &str)
{
    QSqlQuery query(db);
    QString GetStage = "select Stage from StuInfo where id = " + StuId;
    if(query.exec(GetStage))
        if(query.next())
            str = query.value("Stage").toString();
}
